package dbmapper;

import dbconnection.DBConnectionService;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javabean.UserBean;

public class UserMapper extends DBMapper {

    public UserBean isExist(String username, String password) throws Exception {
        UserBean user = null;
        Statement st = con.createStatement();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * FROM users1 WHERE ");
        sqlStr.append(" username = '" + username + "'" + " AND ");
        sqlStr.append(" password = '" + password + "'");
        ResultSet rs = st.executeQuery(sqlStr.toString());
        if (rs != null && rs.next()) {
            user = new UserBean();
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setSex(rs.getString("sex"));
            user.setFullname(rs.getString("fullname"));
            user.setDOB(rs.getDate("DOB"));
            user.setEmail(rs.getString("email"));
            user.setAddress(rs.getString("address"));
            user.setRole(rs.getString("role"));
        }

        return user;
    }

    public void createNewUser(UserBean user) {
        try {
            Statement st = con.createStatement();
            StringBuffer sqlStr = new StringBuffer();
            //Insert into TTSV.[dbo].[users1] values ('ngochai_rita','baongoc','nu','Tran Ngoc Hai','05/06/2005','ngochai_rita2010@yahoo.com','sdfghjkqwertyuiop','user');
            sqlStr.append(" INSERT INTO users1 VALUES ('" + user.getUsername() + "','" + user.getPassword() + "','" + user.getSex() + "','" + user.getFullname() + "','" + user.getDOB() + "','" + user.getEmail() + "','" + user.getAddress() + "','user'");
            st.executeQuery(sqlStr.toString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void deleteSpecifiedUser(String username) throws SQLException {
        try {
            Statement st = con.createStatement();
            StringBuffer sqlStr = new StringBuffer();
            sqlStr.append(" Delete from users1 where username='" + username + "' ");
            st.executeQuery(sqlStr.toString());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public String updateSpecifiedUser(UserBean user) {
        String username = "";

        return username;
    }

    public ArrayList<UserBean> searchUser(UserBean user) {
        ArrayList listOfUsers = new ArrayList<UserBean>();


        return listOfUsers;
    }

    public ArrayList<UserBean> listAllUser() throws Exception {
        ArrayList listOfUsers = new ArrayList<UserBean>();
        Statement st = con.createStatement();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * FROM users1 ");
        ResultSet rs = st.executeQuery(sqlStr.toString());
        while (rs != null && rs.next()) {
            UserBean user = new UserBean();
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setSex(rs.getString("sex"));
            user.setFullname(rs.getString("fullname"));
            user.setDOB(rs.getDate("DOB"));
            user.setEmail(rs.getString("email"));
            user.setAddress(rs.getString("address"));
            user.setRole(rs.getString("role"));
            listOfUsers.add(user);
        }

        return listOfUsers;
    }
}
